/*
 * @Description: 
 * @Author: 47
 * @Date: 2023-01-03 14:22:20
 * @LastEditors: 47
 * @LastEditTime: 2023-02-18 16:13:23
 */
// 引入连接池
const db = require('../db')

//根据“userId”获取我的全部考试信息
const getMyTestList = (req, res) => {
    let userId = req.params.userId
    let sql = `SELECT users.user_id,users.user_name,test.test_id,test.test_title,test.test_start_time,test.test_end_time,test.test_content,test.test_status
                FROM user_test_rel
                JOIN users ON user_test_rel.user_id = users.user_id
                JOIN test ON user_test_rel.test_id = test.test_id
                WHERE users.user_id = ?`
    let data = [userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取我的全部考试信息成功',
            data: result
        })


    })
}


//根据“courseId”获取课程考试列表
const getCourseTestList = (req, res) => {
    let userId = req.params.userId
    let courseId = req.params.courseId
    let sql = `SELECT
    my_course.my_course_id,
    my_course.my_course_name,
    test.test_id,
    test.test_title,
    test.test_start_time,
    test.test_end_time,
    user_test_rel.test_status
    FROM
    course_test_rel
    JOIN my_course ON course_test_rel.course_id = my_course.my_course_id
    JOIN test ON course_test_rel.test_id = test.test_id
    JOIN user_test_rel ON user_test_rel.test_id = test.test_id
    WHERE
    my_course.my_course_id = ? AND
    user_test_rel.user_id = ?`
    let data = [courseId,userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取课程考试列表成功',
            data: result
        })


    })

}


//根据“testId”获取考试详情
const getCourseTestDetail = (req, res) => {
    let testId = req.params.testId
    let sql = `SELECT * FROM test WHERE test.test_id = ?`
    let data = [testId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }


        // if (!result.length) {
        //     //失败
        //     res.status(200).json({
        //         code: 400,
        //         msg: '没有考试详情',
        //         data: null
        //     })
        // }
        let testObj = result[0]
        testObj.code = 2

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取考试详情成功',
            data: testObj
        })


    })

}


//根据“courseId”获取课程作业列表
const getCourseHomeworkList = (req, res) => {
    let userId = req.params.userId
    let courseId = req.params.courseId
    let sql = `SELECT
    my_course.my_course_id,
    my_course.my_course_name,
    homeworks.homework_id,
    homeworks.homework_title,
    homeworks.homework_start_time,
    homeworks.homework_end_time,
    user_homework_rel.homework_status
    FROM
    course_homework_rel
    JOIN my_course ON course_homework_rel.course_id = my_course.my_course_id
    JOIN homeworks ON course_homework_rel.homework_id = homeworks.homework_id
    JOIN user_homework_rel ON user_homework_rel.homework_id = homeworks.homework_id
    WHERE
    my_course.my_course_id = ? AND
    user_homework_rel.user_id = ?
    `
    let data = [courseId,userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取课程作业列表成功',
            data: result
        })


    })
}


//根据“homeworkId”获取作业详情
const getCourseHomeworkDetail = (req, res) => {
    let homeworkId = req.params.homeworkId
    let sql = `SELECT
    homeworks.homework_id,
    homeworks.homework_title,
    homeworks.homework_start_time,
    homeworks.homework_end_time,
    homeworks.homework_content
    FROM
    homeworks
    WHERE
    homeworks.homework_id = ?`
    let data = [homeworkId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        let homeworkObj = result[0]
        homeworkObj.code = 1

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取作业详情成功',
            data: homeworkObj
        })


    })

}



//根据“courseId”获取章节标题
const getChapterInfo = (req, res) => {
    //根据“courseId”查询一级列表
    let userId = req.params.userId
    let courseId = req.params.courseId
    let sql = `SELECT
    chapters.chapter_id,
    chapters.chapter_title
    FROM
    course_chapter_rel
    JOIN my_course ON course_chapter_rel.course_id = my_course.my_course_id
    JOIN chapters ON course_chapter_rel.chapter_id = chapters.chapter_id
    WHERE
    my_course.my_course_id = ?
    ORDER BY chapters.chapter_id ASC
    `
    let data = [courseId]
    db.query(sql, data, (err, chapterOneList) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        let chapterInfo = []

        if (!chapterOneList.length) {
            res.status(200).json({
                code: 400,
                msg: '没有章节',
                data: chapterInfo
            })
        }

        //遍历一级列表获取每个一级对象的id
        chapterOneList.forEach(chapterOneObj => {
            let sql = `SELECT
                chapters.chapter_id,
                chapters.chapter_title
                FROM
                chapters
                WHERE
                chapters.chapter_pid = ?
                ORDER BY
                chapters.chapter_id ASC
            `
            let data = [chapterOneObj.chapter_id]
            db.query(sql, data, (err, chapterTwoList) => {
                //err出错
                if (err) {
                    return res.status(500).json({
                        code: 500,
                        msg: '服务器出错误，请联系管理员',
                        data: null
                    })
                }
                if (chapterTwoList.length) {
                    let chapterTwoListArr = []
                    chapterTwoList.forEach(chapterTwoObj => {
                        let sql = `SELECT
                        user_chapter_rel.chapter_status
                        FROM
                        user_chapter_rel
                        WHERE
                        user_chapter_rel.chapter_id = ?
                        AND 
                        user_chapter_rel.user_id = ?
                        `
                        let data = [chapterTwoObj.chapter_id, userId]
                        db.query(sql, data, (err, chapterTwoStatus) => {
                            //err出错
                            if (err) {
                                return res.status(500).json({
                                    code: 500,
                                    msg: '服务器出错误，请联系管理员',
                                    data: null
                                })
                            }
                            if (chapterTwoStatus[0]) {
                                chapterTwoObj.chapter_status = chapterTwoStatus[0].chapter_status
                                    // console.log(chapterTwoObj);
                                chapterTwoListArr.push(chapterTwoObj)
                            }



                            if (chapterTwoListArr.length == chapterTwoList.length) {
                                //把二级列表添加到一级对象中
                                chapterOneObj.chapterTwoList = chapterTwoList

                                //把一级对象添加到章节的数组中
                                chapterInfo.push(chapterOneObj)

                                //遍历完所有对象后再将数据返回
                                if (chapterInfo.length == chapterOneList.length) {
                                    res.status(200).json({
                                        code: 200,
                                        msg: '获取章节标题成功',
                                        data: chapterInfo
                                    })
                                }


                            }



                        })
                    })
                }
            })
        })

    })
}


//根据“chapterId”查询对应的章节详情
const getChapterDetail = (req, res) => {
    let chapterId = req.params.chapterId
    let userId = req.params.userId
    let sql = `SELECT
    chapters.chapter_id,
    chapters.chapter_title
    FROM
    chapters
    WHERE
    chapters.chapter_id = ?`
    let data = [chapterId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        let chapterDetail = result[0]

        let sql = `SELECT
        detail.detail_id,
        detail.detail_content,
        detail.detail_url,
        detail.detail_type,
        user_detail_rel.detail_status
        FROM
        chapter_detail_rel
        JOIN chapters ON chapter_detail_rel.chapter_id = chapters.chapter_id
        JOIN detail ON chapter_detail_rel.detail_id = detail.detail_id
        JOIN user_detail_rel ON user_detail_rel.detail_id = detail.detail_id
        WHERE
        chapters.chapter_id = ? AND
        user_detail_rel.user_id = ?`
        let data = [chapterId, userId]

        db.query(sql, data, (err, detailList) => {
            //err出错
            if (err) {
                return res.status(500).json({
                    code: 500,
                    msg: '服务器出错误，请联系管理员',
                    data: null
                })
            }
            if (detailList.length) {
                chapterDetail.detailList = detailList
            }


            //成功
            res.status(200).json({
                code: 200,
                msg: '查询章节详情成功',
                data: chapterDetail
            })

        })



    })

}



//根据detailId更新章节详情里的任务点
const updateDetailTaskPoint = (req, res) => {
    let detailId = req.params.detailId;
    let userId = req.params.userId;
    let sql = `update user_detail_rel set detail_status =  1 where detail_id = ? AND user_id = ?`
    let data = [detailId, userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '更新章节详情里的任务点成功',
            data: null
        })

    })

}


//根据chapterId更新章节标题里的任务点
const updateChapterTaskPoint = (req, res) => {
    let chapterId = req.params.chapterId;
    let userId = req.params.userId;
    let sql = `update user_chapter_rel set chapter_status =  1 where chapter_id = ? AND user_id = ?`
    let data = [chapterId, userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '更新章节标题里的任务点成功',
            data: null
        })

    })

}


// 获取自选课程列表
const getMoreCourseList = (req, res) => {

    let sql = 'SELECT * FROM courses'
    db.query(sql, (err, results) => {
        // 如果出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        res.status(200).json({
            code: 200,
            msg: '获取自选课程列表',
            data: results
        })
    })
}

// 修改报名状态
const updateCourseStatus = (req, res) => {

    // 课程id
    let courseId = req.params.courseId
    let sql = "UPDATE courses SET course_status = '1' WHERE course_id = ? "
    let data = [courseId]

    db.query(sql, data, (err, results) => {

        // 如果出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        res.status(200).json({
            code: 200,
            msg: '修改报名状态成功',
            data: null
        })
    })
}

// 获取我的课程列表
const getMyCourseList = (req, res) => {

    // 课程id
    let userId = req.params.userId
    let sql = "SELECT users.user_id,users.user_name,my_course.my_course_name,my_course.my_course_teacher, my_course.my_course_img,my_course.my_course_id,my_course_msg FROM user_mycourse_rel JOIN users ON user_mycourse_rel.user_id = users.user_id JOIN my_course ON user_mycourse_rel.mycourse_id = my_course.my_course_id WHERE users.user_id = ? "
    let data = [userId]

    // 如果有关键字
    if (req.params.keyword) {
        sql += "AND my_course.my_course_name LIKE ?"
        keyword = "%" + req.params.keyword + "%"
        data.push(keyword)
    }

    db.query(sql, data, (err, results) => {
        // 如果出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        res.status(200).json({
            code: 200,
            msg: '获取我的课程列表',
            data: results
        })

        // 如果有关键字
        // if (req.params.keyword) {
        //     sql += "AND my_course.my_course_name LIKE ?"
        //     let keyword = "%" + req.params.keyword + "%"
        //     data.push(keyword)

        //     db.query(sql, data, (err, keywordResults) => {

        //         // 如果出错
        //         if (err) {
        //             return res.status(500).json({
        //                 code: 500,
        //                 msg: '服务器出错误，请联系管理员',
        //                 data: null
        //             })
        //         }

        //         res.status(200).json({
        //             code: 200,
        //             msg: '带有关键字搜索我的课程列表',
        //             data: keywordResults
        //         })
        //     })

        // } else {
        //     res.status(200).json({
        //         code: 200,
        //         msg: '获取我的课程列表',
        //         data: results
        //     })
        // }


    })
}

// 修改报名状态
// 1.根据 courseId 查课程信息  SELECT * FROM my_course WHERE my_course_id = 1  
// 3.INSERT INTO my_course(my_course_name,my_course_teacher,my_course_img,my_course_msg) VALUES(?,?,?,?); set @id = @@IDENTITY; INSERT INTO user_mycourse_rel(user_id,mycourse_id) VALUES(?,@id);
const addMyCourse = (req, res) => {

    // 课程id
    let courseId = req.body.courseId;
    // 用户id
    let userId = req.body.userId
    let sql = "SELECT * FROM courses WHERE course_id = ?"
    let data = [courseId]

    // 根据 courseId 查课程信息
    db.query(sql, data, (err, courseInfo) => {
        console.log(sql, data);
        console.log(courseInfo);

        // 如果出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        sql = 'INSERT INTO my_course(my_course_name,my_course_teacher,my_course_img,my_course_msg) VALUES(?,?,?,?);set @id = @@IDENTITY;INSERT INTO user_mycourse_rel(user_id,mycourse_id) VALUES(?,@id);'
        data = [courseInfo[0].course_name, courseInfo[0].course_teacher, courseInfo[0].course_img, courseInfo[0].course_msg, userId];
        // 将自选课程添加到我的课程
        db.query(sql, data, (err, results) => {
            // 如果出错
            if (err) {
                return res.status(500).json({
                    code: 500,
                    msg: '服务器出错误，请联系管理员',
                    data: err
                })
            }

            res.status(200).json({
                code: 200,
                msg: '自选课添加成功！',
                data: null
            })
        })


    })
}

//根据“courseId”和"userId"获取课程下的通知列表
const getCourseNoticeList = (req, res) => {
    let courseId = req.params.courseId
    let userId = req.params.userId
    let sql = `SELECT
    my_course.my_course_id,
    my_course.my_course_name,
    notices.notice_id,
    notices.notice_title,
    notices.notice_time,
    user_notice_rel.notice_status
    FROM
    course_notice_rel
    JOIN my_course ON course_notice_rel.course_id = my_course.my_course_id
    JOIN notices ON course_notice_rel.notice_id = notices.notice_id
    JOIN user_notice_rel ON user_notice_rel.notice_id = notices.notice_id
    WHERE
    my_course.my_course_id = ? AND
    user_notice_rel.user_id = ?
    `
    let data = [courseId, userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取课程下的通知列表成功',
            data: result
        })


    })

}

//根据“noticeId”获取通知详情页信息以及对应的评论区
const getNoticeDetail = (req, res) => {
    let userId = req.params.userId
    let noticeId = req.params.noticeId
    let sql = `SELECT
    notices.notice_id,
    notices.notice_title,
    notices.notice_time,
    notices.notice_teacher,
    notices.notice_student,
    notices.notice_content,
    notices.notice_fileurl,
    notices.notice_like_count,
    user_notice_rel.like_status
    FROM
    notices
    JOIN user_notice_rel ON user_notice_rel.notice_id = notices.notice_id
    WHERE
    notices.notice_id = ? AND
    user_notice_rel.user_id = ?`
    let data = [noticeId,userId]
    let noticeDetail = {}

    db.query(sql, data, (err, noticeMsg) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }
        noticeDetail.noticeMsg = noticeMsg[0]

        //获取评论列表
        let sql = `SELECT
          notices.notice_id,
          comments.comment_id,
          comments.comment_content,
          comments.comment_time,
          comments.like_count,
          comments.comment_type,
          users.user_id,
          users.user_name,
          users.user_img
          FROM
          notice_comment_rel
          JOIN notices ON notice_comment_rel.notice_id = notices.notice_id
          JOIN comments ON notice_comment_rel.comment_id = comments.comment_id
          JOIN user_comment_rel ON user_comment_rel.comment_id = comments.comment_id
          JOIN users ON user_comment_rel.user_id = users.user_id
          WHERE
          notices.notice_id = ?
          ORDER BY
          comments.comment_time DESC
          `
        let data = [noticeId]

        db.query(sql, data, (err, commentList) => {
            if (err) {
                return res.status(500).json({
                    code: 500,
                    msg: '服务器出错误，请联系管理员',
                    data: null
                })
            }

            noticeDetail.commentList = commentList

            //成功
            res.status(200).json({
                code: 200,
                msg: '获取通知详情页信息以及对应的评论区成功',
                data: noticeDetail
            })

        })
    })

}

//根据“courseId”获取课程的讨论列表 
const getCourseDiscussList = (req, res) => {
    let courseId = req.params.courseId
    let userId = req.params.userId
    let sql = `SELECT
    my_course.my_course_id,
    my_course.my_course_name,
    discuss.discuss_id,
    discuss.discuss_author,
    discuss.discuss_time,
    discuss.discuss_content,
    discuss.discuss_like_count
    FROM
    course_discuss_rel
    JOIN my_course ON course_discuss_rel.course_id = my_course.my_course_id
    JOIN discuss ON course_discuss_rel.discuss_id = discuss.discuss_id
    WHERE
  my_course.my_course_id = ?
  `
    let data = [courseId]

    let discussList = []
    db.query(sql, data, (err, sqlDiscussList) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }
        if(!sqlDiscussList.length){
            res.status(200).json({
                code: 400,
                msg: '暂时还没有讨论',
                data: null
            })
        }

        sqlDiscussList.forEach(discussObj => {
            let sql = `SELECT
            user_discuss_rel.like_status
            FROM
            user_discuss_rel
            WHERE
            user_discuss_rel.user_id = ? AND
            user_discuss_rel.discuss_id = ?
            `
            let data = [userId,discussObj.discuss_id]
            db.query(sql, data, (err,likeStatus) => {
                if (err) {
                    return res.status(500).json({
                        code: 500,
                        msg: '服务器出错误，请联系管理员',
                        data: null
                    })
                }
                
                discussObj.like_status = likeStatus[0].like_status
                discussList.push(discussObj) 

                if (sqlDiscussList.length == discussList.length) {
                    //成功
                    res.status(200).json({
                        code: 200,
                        msg: '获取课程的讨论列表成功',
                        data: discussList
                    })
                }

            })
            
        })



    })

}

//根据“discussId”获取讨论详情页信息以及对应的评论区 
const getDiscussDetail = (req, res) => {
    let discussId = req.params.discussId
    let userId = req.params.userId
    let sql = `SELECT
    discuss.discuss_id,
    discuss.discuss_author,
    discuss.discuss_time,
    discuss.discuss_content,
    discuss.discuss_like_count,
    user_discuss_rel.user_id,
    user_discuss_rel.like_status
    FROM
    discuss
    JOIN user_discuss_rel ON user_discuss_rel.discuss_id = discuss.discuss_id
    WHERE
    discuss.discuss_id  = ?  AND
    user_discuss_rel.user_id = ?`
    let data = [discussId,userId]
    let discussDetail = {}

    db.query(sql, data, (err, discussMsg) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }
        discussDetail.discussMsg = discussMsg[0]

        //获取评论列表
        let sql = `SELECT
          discuss.discuss_id,
          comments.comment_id,
          comments.comment_content,
          comments.comment_time,
          comments.like_count,
          comments.comment_type,
          users.user_id,
          users.user_name,
          users.user_img
          FROM
          discuss_comment_rel
          JOIN discuss ON discuss_comment_rel.discuss_id = discuss.discuss_id
          JOIN comments ON discuss_comment_rel.comment_id = comments.comment_id
          JOIN user_comment_rel ON user_comment_rel.comment_id = comments.comment_id
          JOIN users ON user_comment_rel.user_id = users.user_id
          WHERE
          discuss.discuss_id = ?`
        let data = [discussId]

        db.query(sql, data, (err, commentList) => {
            if (err) {
                return res.status(500).json({
                    code: 500,
                    msg: '服务器出错误，请联系管理员',
                    data: null
                })
            }

            discussDetail.commentList = commentList

            //成功
            res.status(200).json({
                code: 200,
                msg: '获取讨论详情页信息以及对应的评论区',
                data: discussDetail
            })

        })
    })

}

//根据“courseId,userId”获取课程的签到列表
const getCourseSignList = (req, res) => {
    let courseId = req.params.courseId
    let userId = req.params.userId
    let sql = `SELECT
    my_course.my_course_id,
    my_course.my_course_name,
    signs.sign_id,
    signs.sign_title,
    signs.sign_time,
    user_sign_rel.sign_status
    FROM
    course_sign_rel
    JOIN my_course ON course_sign_rel.course_id = my_course.my_course_id
    JOIN signs ON course_sign_rel.sign_id = signs.sign_id
    JOIN user_sign_rel ON user_sign_rel.sign_id = signs.sign_id
    WHERE
    my_course.my_course_id = ? AND
    user_sign_rel.user_id = ?
    `
    let data = [courseId, userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取课程的签到列表成功',
            data: result
        })


    })

}


//根据noticeId和userId更新通知状态
const updateNoticeStatus = (req, res) => {
    let noticeId = req.params.noticeId;
    let userId = req.params.userId;
    let sql = `UPDATE user_notice_rel  set notice_status = 1  
          WHERE
          notice_id = ? AND user_id = ? `
    let data = [noticeId, userId]
    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '更新通知状态成功',
            data: null
        })

    })

}


//根据signId和userId更新签到状态
const updateSignStatus = (req, res) => {
    let signId = req.params.signId;
    let userId = req.params.userId;
    let sql = `UPDATE user_sign_rel  set sign_status = 1  
        WHERE
        sign_id = ? AND user_id = ? `
    let data = [signId, userId]
    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '更新签到状态成功',
            data: null
        })

    })

}


//更新签到时间 
const getSignTime = (req, res) => {
    let signSuccessTime = req.body.signSuccessTime;
    let signId = req.body.signId;
    let userId = req.body.userId;
    let sql = `UPDATE user_sign_rel  set sign_success_time = ? 
          WHERE
          sign_id = ? AND user_id = ? `
    let data = [signSuccessTime, signId, userId]
    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取签到时间成功',
            data: null
        })

    })

}

// 获取签到成功时间
const getSignSuccessTime = (req, res) => {
    let signId = req.body.signId;
    let userId = req.body.userId;
    let sql = `SELECT * FROM
    user_sign_rel
    WHERE
    user_sign_rel.user_id = ? AND
    user_sign_rel.sign_id = ?`
    let data = [userId, signId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '获取签到时间成功',
            data: result
        })

    })

}

// (教师端添加课程章节使用)  学生端查看只需要根据courseId查询my_course表下的课程任务点数量
//根据courseId查询该课程下的全部任务点数量
//1 先查出该课程下一级标题有哪些
//2 遍历一级标题查出每个一级标题下二级标题的数量
//3 将查到的数量 加起来更新到数据库中
const updateCourseTaskPointNum = (req, res) => {
    let courseId = req.params.courseId;
    let sql = `SELECT
    chapter_id
    FROM
    course_chapter_rel
    WHERE
    course_chapter_rel.course_id = ? `
    let data = [courseId]
    db.query(sql, data, (err, oneChapterList) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }
        if (!oneChapterList.length) {
            return res.status(200).json({
                code: 400,
                msg: '该课程下还没有任务点',
                data: null
            })
        }

        let arr = []
        let TaskPointNum = 0

        oneChapterList.forEach(chapterOneObj => {
            let sql = `SELECT
            count(*) as theNum
            FROM
            chapters
            WHERE
            chapters.chapter_pid = ? `
            let data = [chapterOneObj.chapter_id]
            db.query(sql, data, (err, theChapterTaskNum) => {
                //err出错
                if (err) {
                    return res.status(500).json({
                        code: 500,
                        msg: '服务器出错误，请联系管理员',
                        data: null
                    })
                }

                let num = theChapterTaskNum[0].theNum
                TaskPointNum = TaskPointNum+num
                arr.push(num)

                if (arr.length == oneChapterList.length ) {
                    let sql = `UPDATE my_course
                    SET my_course_taskPointNum = ?
                    WHERE my_course_id = ? `
                    let data = [TaskPointNum,courseId]

                    db.query(sql, data, (err, result) => {
                        //err出错
                        if (err) {
                            return res.status(500).json({
                                code: 500,
                                msg: '服务器出错误，请联系管理员',
                                data: null
                            })
                        }
                        //成功
                        res.status(200).json({
                            code: 200,
                            msg: '获取该课程下的任务点数量成功',
                            data:TaskPointNum
                        })
                    })
                     
                }





            })





        })

        



    })

}

//学生端根据courseId查询my_course表下的课程任务点数量
//根据courseId获取该课程下的任务点数量
const getCourseTaskPointNum = (req, res) => {
    let courseId = req.params.courseId
    let sql = `SELECT
    my_course.my_course_taskPointNum
    FROM
    my_course
    WHERE
    my_course.my_course_id = ? `
    let data = [courseId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }
        //成功
        res.status(200).json({
            code: 200,
            msg: '根据courseId获取该课程下的任务点数量成功',
            data: result[0]
        })
    })

}


//根据userId和courseId查询这个课程下我已经完成的任务点 (每次进入页面挂载的时候都需要更新然后展示，所有不需要在数据库中存储已经完成的任务点)
const getMyOkTaskNumTheCourse = (req, res) => {
    let courseId = req.params.courseId
    let userId = req.params.userId
    let sql = `SELECT
    chapter_id
    FROM
    course_chapter_rel
    WHERE
    course_chapter_rel.course_id = ? `
    let data = [courseId]

    db.query(sql, data, (err, oneChapterList) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: null
            })
        }
        if (!oneChapterList.length) {
            return res.status(200).json({
                code: 400,
                msg: '该课程下还没有任务点',
                data: null
            })
        }

        let arr = []
        let OkTaskNum = 0
        let myOkTaskNum = {}
        oneChapterList.forEach(chapterOneObj => {
            let sql = `SELECT
            count(*) as theOkNum
            FROM
            chapters
            JOIN user_chapter_rel ON user_chapter_rel.chapter_id = chapters.chapter_id
            WHERE
            chapters.chapter_pid = ? AND
            user_chapter_rel.chapter_status = 1 AND
            user_chapter_rel.user_id = ?`
            let data = [chapterOneObj.chapter_id,userId]
            db.query(sql, data, (err, theChapterOkTaskNum) => {
                //err出错
                if (err) {
                    return res.status(500).json({
                        code: 500,
                        msg: '服务器出错误，请联系管理员',
                        data: null
                    })
                }

                let num = theChapterOkTaskNum[0].theOkNum
                OkTaskNum = OkTaskNum+num
                arr.push(num)

                if (arr.length == oneChapterList.length ) {
                    // let sql = `UPDATE user_mycourse_rel
                    // SET myOkTaskPoint = ?
                    // WHERE user_id = ?
                    // AND mycourse_id = ?`
                    // let data = [OkTaskNum,userId,courseId]

                    // db.query(sql, data, (err, result) => {
                        //err出错
                        // if (err) {
                        //     return res.status(500).json({
                        //         code: 500,
                        //         msg: '服务器出错误，请联系管理员',
                        //         data: null
                        //     })
                        // }
                        myOkTaskNum.OkTaskNum = OkTaskNum
                        //成功
                        res.status(200).json({
                            code: 200,
                            msg: '查询这个课程下我已经完成的任务点',
                            data:myOkTaskNum
                        })
                    // })
                     
                }
            })

        })
    })

}


//根据userId和homeworkId/testId添加作业/考试内容
const addContent = (req, res) => {
    let id = req.body.id;
    let userId = req.body.userId;
    let content = req.body.content;
    let judgeCode = req.body.judgeCode;
    let submitTime = req.body.submitTime;
    let sql = judgeCode == 1 ?
     `UPDATE user_homework_rel  set homework_content = ? ,homework_submitTime = ? , homework_status = 1 WHERE homework_id = ? AND user_id = ? ` : 
     `UPDATE user_test_rel  set test_content = ? ,test_submitTime = ? , test_status = 1 WHERE test_id = ? AND user_id = ?`
    let data = [content,submitTime,id,userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: err
            })
        }

        //成功
        res.status(200).json({
            code: 200,
            msg: '提交作业/考试成功',
            data: content
        })

    })

}

//点赞
const toLike = (req, res) => {
    let id = req.body.id;
    let userId = req.body.userId;
    let likeStatus = req.body.likeStatus == 1 ? 0 : 1;
    let judgeCode = req.body.judgeCode;
    let sql = 
    judgeCode == 1 ?
    `UPDATE user_notice_rel  set like_status = ? WHERE notice_id = ? AND user_id = ? ;         
     UPDATE notices
     SET notice_like_count =(
         SELECT * FROM
             (
                 SELECT count(*)
                 from user_notice_rel
                 where notice_id = ? AND like_status = 1 
             ) as c
     )
     where notice_id = ?` 
     :
     `UPDATE user_discuss_rel  set like_status = ? WHERE discuss_id = ? AND user_id = ? ;         
     UPDATE discuss
     SET discuss_like_count =(
         SELECT * FROM
             (
                 SELECT count(*)
                 from user_discuss_rel
                 where discuss_id = ? AND like_status = 1 
             ) as c
     )
     where discuss_id = ?`

    let data = [likeStatus,id,userId,id,id]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: err
            })
        }


        //成功
        res.status(200).json({
            code: 200,
            msg: '点赞成功',
            data: null
        })

    })

}


// 添加通知/讨论评论
const addComment = (req, res) => {
    // 通知/讨论id
    let id = req.body.id;
    let userId = req.body.userId;
    let commentContent = req.body.commentContent;
    let commentTime = req.body.commentTime;
    let commentType = req.body.commentType;
    //区别通知的评论还是讨论的评论,code = 1  表示给通知添加评论 
    let judgeCode = req.body.code;
    let sql = 
    judgeCode == 1 ?
     `INSERT INTO comments(comment_content,comment_time,comment_type) VALUES(?,?,?);
     set @id = @@IDENTITY;
     INSERT INTO notice_comment_rel(notice_id,comment_id) VALUES(?,@id);
     INSERT INTO user_comment_rel(user_id,comment_id) VALUES(?,@id);` 
     : 
     `INSERT INTO comments(comment_content,comment_time,comment_type) VALUES(?,?,?);
     set @id = @@IDENTITY;
     INSERT INTO discuss_comment_rel(discuss_id,comment_id) VALUES(?,@id);
     INSERT INTO user_comment_rel(user_id,comment_id) VALUES(?,@id);`
    let data = [commentContent,commentTime,commentType,id,userId]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: err
            })
        }


        //成功
        res.status(200).json({
            code: 200,
            msg: '添加评论成功',
            data: null
        })

    })

}

//查询通知/讨论点赞用户列表
const getLikeList = (req, res) => {
    let id = req.params.id;
    let judgeCode = req.params.judgeCode;
    let sql = 
    judgeCode == 1 ?
    `SELECT
    users.user_id,
    users.user_name
    FROM
    user_notice_rel
    INNER JOIN users ON user_notice_rel.user_id = users.user_id
    WHERE
    user_notice_rel.notice_id = ? AND
    user_notice_rel.like_status = 1 ` 
     :
     `SELECT
     users.user_id,
     users.user_name
     FROM
     users
     INNER JOIN user_discuss_rel ON user_discuss_rel.user_id = users.user_id
     WHERE
     user_discuss_rel.discuss_id = ? AND
     user_discuss_rel.like_status = 1
     `
    let data = [id]

    db.query(sql, data, (err, result) => {
        //err出错
        if (err) {
            return res.status(500).json({
                code: 500,
                msg: '服务器出错误，请联系管理员',
                data: err
            })
        }


        //成功
        res.status(200).json({
            code: 200,
            msg: '查询成功',
            data: result
        })

    })

}


module.exports = {
    getCourseTestList,
    getMyTestList,
    getCourseTestDetail,
    getCourseHomeworkList,
    getCourseHomeworkDetail,
    getChapterInfo,
    getChapterDetail,
    updateDetailTaskPoint,
    updateChapterTaskPoint,
    getMoreCourseList,
    updateCourseStatus,
    getMyCourseList,
    addMyCourse,
    getCourseNoticeList,
    getNoticeDetail,
    getCourseDiscussList,
    getDiscussDetail,
    getCourseSignList,
    updateNoticeStatus,
    updateSignStatus,
    getSignTime,
    updateCourseTaskPointNum,
    getCourseTaskPointNum,
    getMyOkTaskNumTheCourse,
    getSignSuccessTime,
    addContent,
    toLike,
    addComment,
    getLikeList
}